﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;

namespace DAO_ADMIN
{
    public class DataProvider
    {
        public static string connectionString = @"Data Source=.;Initial Catalog=HuongRung_Team_19;Integrated Security=True";
        private static SqlConnection _con = null;
        public static SqlConnection CreateSQLConnection()
        {
            string strConnection = @"Data Source=.;Initial Catalog=HuongRung_Team_19;Integrated Security=True";
            _con = new SqlConnection(strConnection);
            return _con;
        }
        public static DataTable Query(string query)
        {
            DataTable data = new DataTable();
            SqlConnection cn = new SqlConnection(connectionString);
            cn.Open();
            SqlCommand cmd = new SqlCommand(query, cn);

            SqlDataAdapter da = new SqlDataAdapter(cmd);

            da.Fill(data);
            return data;
        }
        public SqlDataReader ExcuteReader(SqlCommand command)
        {
            SqlDataReader dataReader = null;
            command.Connection = _con;
            _con.Open();
            dataReader = command.ExecuteReader();
            return dataReader;
        }

        public static DataSet NonSelectStoreProcedure(string ProcName)
        {
            DataSet data = new DataSet();
            SqlConnection cn = new SqlConnection(connectionString);
            cn.Open();
            SqlCommand cmd = new SqlCommand(ProcName, cn);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(data);
            cn.Close();
            return data;

        }
        public static DataTable SelectStoreProcedure(string ProcName, List<SqlParameter> ParaArr)
        {
            DataTable data = new DataTable();
            SqlConnection cn = new SqlConnection(connectionString);
            cn.Open();
            SqlCommand cmd = new SqlCommand(ProcName, cn);
            foreach (SqlParameter para in ParaArr)
            {
                cmd.Parameters.Add(para);
            }
            cmd.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(cmd);

            da.Fill(data);
            cn.Close();
            return data;
        }

        public static DataTable SelectStoreProcedure(string ProcName)
        {
            DataTable data = new DataTable();
            SqlConnection cn = new SqlConnection(connectionString);
            cn.Open();
            SqlCommand cmd = new SqlCommand(ProcName, cn);

            cmd.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(cmd);

            da.Fill(data);
            cn.Close();
            return data;
        }

        public static SqlCommand OutPutProc(string procname, List<SqlParameter> ParaArr, SqlParameter output)
        {
            SqlConnection cn = new SqlConnection(connectionString);
            cn.Open();
            SqlCommand cmd = new SqlCommand(procname, cn);
            cmd.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter para in ParaArr)
            {
                cmd.Parameters.Add(para);
            }
            cmd.Parameters.Add(output).Direction = ParameterDirection.Output;
            cmd.ExecuteNonQuery();
            return cmd;

        }

        public static SqlCommand OutPutProc(string procname, SqlParameter output)
        {
            SqlConnection cn = new SqlConnection(connectionString);
            cn.Open();
            SqlCommand cmd = new SqlCommand(procname, cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(output).Direction = ParameterDirection.Output;
            cmd.ExecuteNonQuery();
            return cmd;

        }

        public static void NonqueryProc(string ProcName, List<SqlParameter> ParaArr)
        {
            SqlConnection cn = new SqlConnection(connectionString);
            cn.Open();
            SqlCommand cmd = new SqlCommand(ProcName, cn);
            foreach (SqlParameter para in ParaArr)
            {
                cmd.Parameters.Add(para);
            }
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.ExecuteNonQuery();
            cn.Close();
        }


        public static DataTable ExecuteStoreProc(string storeProcName, List<string> arrParameterName, ArrayList arrParameterValue)
        {
            DataTable dt = new DataTable();
            SqlConnection cn = new SqlConnection(connectionString);
            try
            {

                cn.Open();

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = cn;
                cmd.CommandText = storeProcName;
                cmd.CommandType = CommandType.StoredProcedure;

                //Lay so parameter
                int N = arrParameterName.Count;
                for (int i = 0; i < N; i++)
                {
                    SqlParameter sqlParam = new SqlParameter(arrParameterName[i], arrParameterValue[i]);
                    cmd.Parameters.Add(sqlParam);
                }

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(dt);


            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi: " + ex.Message);
            }
            cn.Close();
            return dt;
        }
        public static int SingleINTOutput(string storeProcName, List<SqlParameter> ParaArr)
        {
            int returnValue = 0;
            SqlConnection cn = new SqlConnection(connectionString);
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = cn;
                cmd.CommandText = storeProcName;
                cmd.CommandType = CommandType.StoredProcedure;

                foreach (SqlParameter para in ParaArr)
                {
                    cmd.Parameters.Add(para);
                }

                cn.Open();

                returnValue = (int)cmd.ExecuteScalar();

                cn.Close();
            }
            catch (Exception)
            {
                
                throw;
            }
            return returnValue;
        }

    }
}
